package dao;

import pojo.Employee;
import utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDAOImpl {

    private static final String TABLE_NAME = "Employee";

    public static void add(Employee employee) throws SQLException {
        String sql = "INSERT INTO " + TABLE_NAME + " (id_number, company_id, employee_email, employee_name, employee_age, employee_gender, employee_phone_number) VALUES (?, ?, ?, ?, ?, ?, ?)";
        JDBCUtils.execute(sql, null, employee.getCompanyID(), employee.getEmployeeEmail(), employee.getEmployeeName(), employee.getEmployeeAge(), employee.getEmployeeGender(), employee.getEmployeePhoneNumber());
    }

    public static void delete(int idNumber) throws SQLException {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE id_number = ?";
        JDBCUtils.execute(sql, idNumber);
    }

    public static void update(Employee employee) throws SQLException {
        String sql = "UPDATE " + TABLE_NAME + " SET company_id = ?, employee_email = ?, employee_name = ?, employee_age = ?, employee_gender = ?, employee_phone_number = ? WHERE id_number = ?";
        JDBCUtils.execute(sql, employee.getCompanyID(), employee.getEmployeeEmail(), employee.getEmployeeName(), employee.getEmployeeAge(), employee.getEmployeeGender(), employee.getEmployeePhoneNumber(), employee.getIdNumber());
    }

    public static Employee getById(int idNumber) throws SQLException {
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE id_number = ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, idNumber);
        if (resultSet.next()) {
            return createEmployeeFromResultSet(resultSet);
        }
        return null;
    }

    public static List<Employee> getAll() throws SQLException {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        while (resultSet.next()) {
            Employee employee = createEmployeeFromResultSet(resultSet);
            employees.add(employee);
        }
        return employees;
    }

    public static int getCount() throws SQLException {
        String sql = "SELECT COUNT(*) AS count FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        if (resultSet.next()) {
            return resultSet.getInt("count");
        }
        return 0;
    }

    public static List<Employee> getByPage(int start, int pageSize) throws SQLException {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, start, pageSize);
        while (resultSet.next()) {
            Employee employee = createEmployeeFromResultSet(resultSet);
            employees.add(employee);
        }
        return employees;
    }

    public static List<Employee> findByKeyword(String keyword) throws SQLException {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE employee_name LIKE ? OR employee_email LIKE ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%", "%" + keyword + "%");
        while (resultSet.next()) {
            Employee employee = createEmployeeFromResultSet(resultSet);
            employees.add(employee);
        }
        return employees;
    }

    public static List<Employee> findByPageAndKeyword(int start, int pageSize, String keyword) throws SQLException {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE employee_name LIKE ? OR employee_email LIKE ? LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%", "%" + keyword + "%", start, pageSize);
        while (resultSet.next()) {
            Employee employee = createEmployeeFromResultSet(resultSet);
            employees.add(employee);
        }
        return employees;
    }

    private static Employee createEmployeeFromResultSet(ResultSet resultSet) throws SQLException {
        int idNumber = resultSet.getInt("id_number");
        int companyId = resultSet.getInt("company_id");
        String employeeEmail = resultSet.getString("employee_email");
        String employeeName = resultSet.getString("employee_name");
        int employeeAge = resultSet.getInt("employee_age");
        String employeeGender = resultSet.getString("employee_gender");
        String employeePhoneNumber = resultSet.getString("employee_phone_number");
        return new Employee(idNumber, companyId, employeeEmail, employeeName, employeeAge, employeeGender, employeePhoneNumber);
    }
}
